Calendar Slicers

The calendar slicer is a specialized slicer for date-time columns coming from a SQL database, especially where the classic date-time data groups (quarter, semester, year, month, week) don't exist. The slicer enables you to filter the query by these groups, without the need to construct any complex formulations. This is an excellent solution for users who are directly querying a SQL database, and cannot manipulate the database and don't want to spend time creating the relevant formulations.

Calendar Slicer Types

Pyramid data modeling enables users to construct these hierarchical date-time groups when designing the ETL, and then add those elements to the query in Discover. However, this solution is only relevant to those users who construct the model in Pyramid; many users query the database directly. In a direct querying scenario, the user may want to add years to the query, while the database only contains a flat date-time hierarchy consisting of date keys. How can this user add 'years' to the query?

Pyramid offers two solutions for this requirement:

• On-the-fly "date part" calculations. Date part calculations produce custom columns which can be added to any part of the query.
• Date Calendars. The Date Calendar is used to filter the query by specified dates or time periods, date ranges, and date formulations. From the calendar slicer, the user selects the required dates or time period and the query is filtered on-the-fly, without creating any custom columns.

Note: Calendar slicers are supported only for SQL models. This functionality is not currently supported for MS OLAP, Tabular, or SAP BW.

Creating a Calendar Slicer

A calendar slicer can only be created using a date-time column. Add the date-time hierarchy (yellow highlight below) to the Filters zone, dropping it onto the Date Calendar sub-menu (purple highlight):

This produces a slicer which is added to the canvas. When you click the slicer, the Date Calendar opens instead of a drop-down list. Continue reading to learn how to navigate the Date Calendar.

Using the Date Calendar

Calendar Type

The calendar slicer features different "Pickers," each designed to meet a different set of user requirements:

Granularity

Rather than displaying dates only, the Date Calendar allows you to specify the desired level of granularity, meaning you can choose to display any of the date-time groupings in the calendar and make your selections accordingly.

• Select Years and then select 2010 to filter the query to display data for 2010.
• Select Semesters and then select 2008 > S2 and 2009 > S2 to filter the query by semester 2 of 2008 and 2009.
• Select Quarters and then select 2008 > Q2 and 2009 > Q2 to filter the query by quarter 2 of 2008 and 2009. (The example above shows this selection.)
• Select Months and then select 2010 > Jul and Aug to filter the query by July and August, 2010.
• Select Weeks and then select 2009 and 1, 2, and 3 to filter the query by weeks 1, 2, and 3 of 2009.
• Select Dates and then select 2009 and January and then 3, 4, 17, 18, and 31 to filter the query by January 3, 4, 17, 18, and 31, 2009.

Calendar Selection

The Date Calendar allows selections for dates that exist in the database only. Dates and periods for which there is no data are grayed out. To make a selection from the calendar, start by selecting the required level of granularity, then select the required date or time period.

To assist in making a selection, use the back and forward arrows to move to the next or previous period, and use the up and down buttons to jump to a different month or year.

Back and Forward Buttons

Use the back and forward buttons to navigate through the calendar at the current level of granularity. For example, at the Dates level the calendar displays dates by month (orange highlight below), click the forward arrow to go to the next month, or the back arrow to go to the previous month (purple arrows). Likewise, at the week level, click forward to go to the next year, or back to go to the previous year.

Up and Down Buttons

Use the up and down buttons (orange highlight above) to jump to a different month or year for the current level of granularity. Up buttons indicate that you can go up a level, while down arrows indicate that you can go down a level, without making a filter selection.

Examples

To follow these examples, use the Sample Demo data model.

Multi Select

The multi select option is supported by the Simple Date Calendar, allowing you to select multiple date-time elements. You can select multiple elements from different granularity levels.

Multi Select is enabled by default; to disable it, clear its checkbox:

Tip: To remove selections from the panel, either click their X icons individually, or click X at the end of the Selections field to remove all.

Quick Selection

The quick selection options provide a shortcut selection for filtering by the current, first, or last date-time period at the current level of granularity.

To make a quick selection:

1. Start by choosing the granularity level as usual.
2. Then, instead of making a selection from the calendar, choose a Quick Selection from the options below.

• Current: Depending on the given level of granularity, selects today's date (if it exists in the system) or the current year, semester, quarter, month, or week. If the current date-time period doesn't exist in the system, the last date-time period will be selected instead.
• First: Selects the first date-time period in the system according to the selected level of granularity. If the granularity is set to Dates, the first date in the system will be selected; if the granularity is set to months, the first month in the system will be selected; and so on.
• Last: Selects the last date-time period in the system according to the selected level of granularity. If the granularity is set to Dates, the last date in the system is selected. If the granularity is set to years, the last year in the system is selected.